import Router from '@koa/router';
import db from '../mysqlHelper.js';
import response from '../response.js';
import { v4 as uuidv4 } from 'uuid';
import crypto from 'crypto';
import moment from 'moment';
import jwt from 'jsonwebtoken';

const tokenSecret = '1234567890';

const router = new Router;


async function createToken(params) {
    let expiresIn = '12h';
    let token = jwt.sign(params, tokenSecret, {
        expiresIn,
    });
    let expTime = '';
    if (isNaN(expiresIn)) {
        let time = expiresIn.substring(0, expiresIn.length - 1);
        let unit = expiresIn.substring(expiresIn.length - 1, expiresIn.length);
        if (unit == 'h' || unit == 'd') {
            expTime = moment().add(time, unit).format('YYYY-MM-DD HH:mm:ss');
        }
    } else {
        expTime = moment().add(expiresIn, 'ms').format('YYYY-MM-DD HH:mm:ss');
    }

    return { token, expTime: expTime };
}

function generatePwd(password, digest = 'hex') {
    //密码加密
    password = password + 'www';
    return crypto.createHash('md5').update(password).digest(digest);
}


//负载均衡检查
router.all('/Home/Check', async (ctx) => {
    ctx.body = 'ok';
});

router.post('/api/oper/create', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("参数不允许为空");
        return;
    }
    if (body.account == null || body.account == undefined) {
        ctx.body = response.fail("account不允许为空");
        return;
    }
    if (body.oper_name == null || body.oper_name == undefined) {
        ctx.body = response.fail("oper_name不允许为空");
        return;
    }
    if (body.type == null || body.type == undefined) {
        ctx.body = response.fail("type不允许为空");
        return;
    }
    if (body.type != 0 && (body.role_id == null || body.role_id == undefined)) {
        ctx.body = response.fail("role_id不允许为空");
        return;
    }

    body.password = generatePwd(body.password);
    let res = await db.create('oper', body);
    if (res instanceof Error) {
        console.log(`创建oper失败`, res);
        ctx.body = response.fail(res.message); return;
    }
    ctx.body = response.success();
});
router.post('/api/oper/update', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("参数不允许为空");
        return;
    }
    if (body.account == null || body.account == undefined) {
        ctx.body = response.fail("account不允许为空");
        return;
    }
    if (body.oper_name == null || body.oper_name == undefined) {
        ctx.body = response.fail("oper_name不允许为空");
        return;
    }
    if (body.type == null || body.type == undefined) {
        ctx.body = response.fail("type不允许为空");
        return;
    }
    if (body.type != 0 && (body.role_id == null || body.role_id == undefined)) {
        ctx.body = response.fail("role_id不允许为空");
        return;
    }
    if (body.password != null && body.password != undefined && body.password != "") {
        body.password = generatePwd(body.password);
    }
    let res = await db.update('oper', body, { account: body.account });
    if (res instanceof Error) {
        console.log(`更新oper失败`, res);
        ctx.body = response.fail(res.message); return;
    }
    ctx.body = response.success();
});

router.post('/api/db/:table/create', async (ctx) => {
    let table = ctx.params.table;
    let body = ctx.request.body;
    if(table=='merchant_oper' && body.password != null && body.password != undefined && body.password != "") {
        body.password = generatePwd(body.password);
    }
    let res = await db.create(table, body);
    if (res instanceof Error) {
        console.log(`${table}表插入记录失败`, res);
        ctx.body = response.fail(res.message);
        return;
    }
    ctx.body = response.success();
})

router.post('/api/db/:table/update', async (ctx) => {
    let table = ctx.params.table;
    let body = ctx.request.body;
    let where = body.where;
    delete body.where;
    if(table=='merchant_oper' && body.password != null && body.password != undefined && body.password != "") {
        body.password = generatePwd(body.password);
    }
    let res = await db.update(table, body, where);
    if (res instanceof Error) {
        console.log(`${table}表更新记录失败`, res);
        ctx.body = response.fail(res.message);
    }
    ctx.body = response.success();
});


router.post('/api/db/:table/delete', async (ctx) => {
    let table = ctx.params.table;
    let body = ctx.request.body;
    let res = await db.deleteRow(table, body);
    if (res instanceof Error) {
        console.log(`${table}表删除记录失败`, res);
        ctx.body = response.fail(res.message);
    }
    ctx.body = response.success();
});

router.post('/api/db/:table/query', async (ctx) => {
    let table = ctx.params.table;
    let body = ctx.request.body;
    if (!body.flelds) body.flelds = '*';
    if (!body.joinTable) body.joinTable = '';
    if (!body.joinCondition) body.joinCondition = '';
    // if (body.area_path == null || body.area_path == undefined) {
    //     ctx.body = response.fail("area_path不允许为空");
    //     return;
    // }
    let inWhere = '';
    if (body.area_path != null && body.area_path != undefined) {
        let areas = await db.query('SELECT area_id FROM `area` WHERE  area_path  LIKE  ?', [`${body.area_path}%`]);
        if (areas instanceof Error) {
            console.log(`查询area失败`, areas, message);
            ctx.body = response.fail(areas.message); return;
        }
        //将所有的area_id放入以逗号分隔的字符串中
        inWhere = areas.map((item) => {
            return item.area_id;
        }).join(',');
        console.log("inWhere", inWhere);
        inWhere = ` area_id IN (${inWhere})`
    }

    //     flelds = '*',
    // joinTable = '',
    // joinCondition = '')
    let res = await db.findAll(table, body.conditions,
        body.orderBy,
        body.direction,
        body.limit,
        body.offset,
        body.needCount,
        body.areas,
        body.flelds,
        body.joinTable,
        body.joinCondition,
        inWhere
    );
    if (res instanceof Error) {
        console.log(`${table}表查询记录失败`, res);
        ctx.body = response.fail(res.message);
    }
    ctx.body = response.success(res);
})


router.post('/api/db/device/getList', async (ctx) => {
    let body = ctx.request.body;
    let where = "";
    if (body.where != null && body.where != undefined) {
        where = body.where;
    }
    else {
        where = "1=1"
    }

    if (body.area_path != null && body.area_path != undefined) {
        let areas = await db.query('SELECT area_id FROM `area` WHERE  area_path  LIKE  ?', [`${body.area_path}%`]);
        if (areas instanceof Error) {
            console.log(`查询area失败`, areas, message);
            ctx.body = response.fail(areas.message); return;
        }
        //将所有的area_id放入以逗号分隔的字符串中
        let inWhere = areas.map((item) => {
            return item.area_id;
        }).join(',');
        console.log("inWhere", inWhere);
        where += ` and area_id IN (${inWhere})`
    }
    if (body.limit) {
        body.offset = body.limit * (body.offset - 1);
        where += ` LIMIT ${body.offset},${body.limit}`;
    }


    let sql = `SELECT  device.status,device.model,device.lat,device.lon,device_real.*,merchant.*   FROM   device   LEFT JOIN   device_real    
    on  device.sn=device_real.sn  LEFT JOIN  merchant on   device.merchant_id   =merchant.merchant_id  WHERE ${where}`;
    console.log("sql", sql);
    let rows = await db.query(sql, null);
    ctx.body = response.success(rows);
});

// router.post('/api/oper/getList', async (ctx) => {
//     let table = ctx.params.table;
//     let body = ctx.request.body;
//     if (!body.flelds) body.flelds = '*';
//     if (!body.joinTable) body.joinTable = '';
//     if (!body.joinCondition) body.joinCondition = '';
//     // if (body.area_path == null || body.area_path == undefined) {
//     //     ctx.body = response.fail("area_path不允许为空");
//     //     return;
//     // }
//     let inWhere='';
//     if (body.area_path != null && body.area_path != undefined) {
//         let areas = await db.query('SELECT area_id FROM `area` WHERE  area_path  LIKE  ?', [`${body.area_path}%`]);
//         if (areas instanceof Error) {
//             console.log(`查询area失败`, areas, message);
//             ctx.body = response.fail(areas.message); return;
//         }
//         //将所有的area_id放入以逗号分隔的字符串中
//         inWhere= areas.map((item) => {
//             return item.area_id;
//         }).join(',');
//         console.log("inWhere", inWhere);
//         inWhere = ` area_id IN (${inWhere})`
//     }

//     //     flelds = '*',
//     // joinTable = '',
//     // joinCondition = '')
//     let res = await db.findAll(table, body.conditions,
//         body.orderBy,
//         body.direction,
//         body.limit,
//         body.offset,
//         body.needCount,
//         body.areas,
//         body.flelds,
//         body.joinTable,
//         body.joinCondition,
//         inWhere
//     );
//     if (res instanceof Error) {
//         console.log(`${table}表查询记录失败`, res);
//         ctx.body = response.fail(res.message);
//     }
//     ctx.body = response.success(res);
// })
router.post('/api/business/login', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("参数不允许为空");
        return;
    }
    if (body.account == null || body.account == undefined) {
        ctx.body = response.fail("account不允许为空");
        return;
    }
    if (body.password != null && body.password != undefined && body.password != "") {
        body.password = generatePwd(body.password);
    }
    let oper = await db.query('select * from oper where account=?', [body.account]);
    if (oper instanceof Error) {
        console.log(`操作员登录失败`, oper);
        ctx.body = response.fail(oper.message); return;
    }
    if (oper.length == 0) {
        console.log(`操作员登录失败,账号不存在`);
        ctx.body = response.fail('账号不存在'); return;
    }
    if (body.password != null && body.password != undefined && body.password != "") {
        if (oper[0].password != body.password) {
            console.log(`操作员登录失败,密码错误`);
            ctx.body = response.fail('密码错误'); return;
        }
    }
    let token = await createToken({ account: body.account, oper_name: oper[0].oper_name });
    let role = null;
    if (oper[0].type == 1) {
        role = await db.query('select * from role where role_id=?', [oper[0].role_id]);
        if (role instanceof Error) {
            console.log(`操作员登录失败`, role);
            ctx.body = response.fail(role.message); return;
        }
    }
    delete oper[0].password;
    ctx.body = response.success({ token: token.token, expTime: token.expTime, role: role, oper: oper[0] });
})

router.post('/api/merchant/login', async (ctx) => {
    let body = ctx.request.body;
    if (body == null) {
        ctx.body = response.fail("参数不允许为空");
        return;
    }
    if (body.account == null || body.account == undefined) {
        ctx.body = response.fail("account不允许为空");
        return;
    }
    if (body.password != null && body.password != undefined && body.password != "") {
        body.password = generatePwd(body.password);
    }
    let oper = await db.query('select * from merchant_oper where account=?', [body.account]);
    if (oper instanceof Error) {
        console.log(`商户登陆失败`, oper);
        ctx.body = response.fail(oper.message); return;
    }
    if (oper.length == 0) {
        console.log(`商户登录失败,账号不存在`);
        ctx.body = response.fail('账号不存在'); return;
    }
    if (body.password != null && body.password != undefined && body.password != "") {
        if (oper[0].password != body.password) {
            console.log(`商户登录失败,密码错误`);
            ctx.body = response.fail('密码错误'); return;
        }
    }
    let token = await createToken({ account: body.account, oper_name: oper[0].oper_name });
    delete oper[0].password;
    ctx.body = response.success({ token: token.token, expTime: token.expTime,  oper: oper[0] });
})


router.post('/api/dataCenter/getOnLine', async (ctx) => {
    let body = ctx.request.body;
    if (body.area_path == null || body.area_path == undefined) {
        ctx.body = response.fail("area_path不允许为空");
        return;
    }
    let areas = await db.query('SELECT area_id FROM `area` WHERE  area_path  LIKE  ?', [`${body.area_path}%`]);
    if (areas instanceof Error) {
        console.log(`查询area失败`, areas, message);
        ctx.body = response.fail(areas.message); return;
    }
    //将所有的area_id放入以逗号分隔的字符串中
    let inStr = areas.map((item) => {
        return item.area_id;
    }).join(',');
    console.log("inStr", inStr);
    let res = await db.query(`SELECT   COUNT(*) as sum  , sum(is_online=1) as is_online  FROM merchant    LEFT JOIN   device  ON device.merchant_id= merchant.merchant_id 
    LEFT JOIN device_real on device_real.sn= device.sn  WHERE area_id IN (${inStr})`);
    if (res instanceof Error) {
        console.log(`查询在线设备失败`, res.message);
        ctx.body = response.fail(res.message); return;
    }
    ctx.body = response.success(res[0]);
});

router.post('/api/dataCenter/getDeviceInfo', async (ctx) => {
    let body = ctx.request.body;
    if (body.area_path == null || body.area_path == undefined) {
        ctx.body = response.fail("area_path不允许为空");
        return;
    }
    let areas = await db.query('SELECT area_id FROM `area` WHERE  area_path  LIKE  ?', [`${body.area_path}%`]);
    if (areas instanceof Error) {
        console.log(`查询area失败`, areas, message);
        ctx.body = response.fail(areas.message); return;
    }
    //将所有的area_id放入以逗号分隔的字符串中
    let inStr = areas.map((item) => {
        return item.area_id;
    }).join(',');
    console.log("inStr", inStr);
    let res = await db.query(`SELECT   merchant.merchant_id,merchant.merchant_name,device_real.*    FROM merchant    LEFT JOIN   device  ON device.merchant_id= merchant.merchant_id 
    LEFT JOIN device_real on device_real.sn= device.sn  WHERE area_id IN (${inStr})`);
    if (res instanceof Error) {
        console.log(`按区域获取设备信息失败`, res.message);
        ctx.body = response.fail(res.message); return;
    }
    ctx.body = response.success(res);
});

//SELECT area_id FROM `area` WHERE  area_path  LIKE  '/%'

router.post('/api/device/getDetail', async (ctx) => {
    let body = ctx.request.body;
    let sql = `SELECT  device.status,device.model,device.lat,device.lon,device_real.*,merchant.*   FROM   device   LEFT JOIN   device_real   
     on  device.sn=device_real.sn  LEFT JOIN  merchant on   device.merchant_id   =merchant.merchant_id `;
    let count = 0;
    
    let inStr='';
    if (body.area_path != null && body.area_path != undefined) {
        let areas = await db.query('SELECT area_id FROM `area` WHERE  area_path  LIKE  ?', [`${body.area_path}%`]);
        if (areas instanceof Error) {
            console.log(`查询area失败`, areas, message);
            ctx.body = response.fail(areas.message); return;
        }
        //将所有的area_id放入以逗号分隔的字符串中
        inStr = areas.map((item) => {
            return item.area_id;
        }).join(',');
        console.log("inStr", inStr);
    }

    if (body.limit != undefined && body.offset != undefined) {
        let countSql = `SELECT  COUNT(*) as count   FROM   device   LEFT JOIN   device_real    on  device.sn=device_real.sn  LEFT JOIN  merchant on   device.merchant_id   =merchant.merchant_id `;
        if (body.where) {
            //countSql += ` WHERE ${body.where}`;
            if(inStr!=''  & body.where!=''){
                countSql += ` WHERE ${body.where} and area_id IN (${inStr})`;
            }else if (inStr!=''  & body.where==''){
                countSql += ` WHERE area_id IN (${inStr})`;
            }
            else{
                countSql += ` WHERE ${body.where}`;
            }
        }
        else if (inStr!='' ){
            countSql += ` WHERE area_id IN (${inStr})`;
        }
        console.log("countSql", countSql);
        let [rows] = await db.query(countSql);
        console.log("rows", rows);
        count = rows.count;
    }
    if (body.where) {
       // sql += ` WHERE ${body.where}`;
        if(inStr!=''  & body.where!=''){
            sql += ` WHERE ${body.where} and area_id IN (${inStr})`;
        }else if (inStr!=''  & body.where==''){
            sql += ` WHERE area_id IN (${inStr})`;
        }
        else{
            sql += ` WHERE ${body.where}`;
        }
    }
    else if (inStr!='' ){
        sql += ` WHERE area_id IN (${inStr})`;
    }
    
    if (body.orderBy) {
        sql += ` ORDER BY ${orderBy} ${direction}`;
    }
    if (body.limit != undefined && body.offset != undefined) {
        body.offset = body.limit * (body.offset - 1);
        sql += ` LIMIT ${body.offset},${body.limit}`;
    }
    try {
        let rows = await db.query(sql, null);

        if (body.limit != undefined && body.offset != undefined)
            return ctx.body = response.success({
                count,
                rows,
            });
        else
            return ctx.body = response.success(rows);

    } catch (error) {
        console.error(
            'findAll error: ',
            error.message,
            table,
            JSON.stringify(conditions)
        );
        return ctx.body = response.fail(error.message);
    }


});




export { router };